<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:output method="text" version="1.0" encoding="UTF-8" indent="yes"/>
	<xsl:template match="/">using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using <xsl:value-of select="CogDataSet/TableInfo/Namespace"/>.Interface;
using IDataAdapter = <xsl:value-of select="CogDataSet/TableInfo/Namespace"/>.Interface.IDataAdapter;

namespace <xsl:value-of select="CogDataSet/TableInfo/Namespace"/>
{
    public partial class <xsl:value-of select="CogDataSet/TableInfo/ClassPrefix"/>DataSet
    {
        private static readonly Dictionary&lt;string, string&gt; SchemaCache =
            new Dictionary&lt;string, string&gt;();
    
        private static IDataAdapter _dataAdapter;

        #region Public Properties
        
        public static IDataAdapter DataAdapter 
        { 
            get
            {
                if(_dataAdapter == null)
                    throw new ApplicationException("DataAdapter not initialized");
                return _dataAdapter;
            }
            set { _dataAdapter = value; }
        }
        
        #endregion

        #region Public Methods
        
        public static void SaveChanges(DataTable table)
        {
            var changes = table.GetChanges();
            if (changes == null || changes.Rows.Count == 0)
                return;
            if (changes.Rows.Cast&lt;DataRow&gt;().Any(r =&gt; r.RowState == DataRowState.Added))
                ExecuteInsert(table);
            if (changes.Rows.Cast&lt;DataRow&gt;().Any(r =&gt; r.RowState == DataRowState.Modified))
                ExecuteUpdate(table);
            if (changes.Rows.Cast&lt;DataRow&gt;().Any(r =&gt; r.RowState == DataRowState.Deleted))
                ExecuteDelete(table);
        }

        public static void ExecuteSelect(IDataTable table)
        {
            var oTable = table as DataTable;
            if (oTable == null)
                return;

            ExecuteQuery(oTable, table.SelectStoredProc);
        }

        public static void ExecuteInsert(DataTable table)
        {
            var oTable = table as IDataTable;
            if (oTable == null)
                return;

            foreach (var row in table.Rows.Cast&lt;DataRow&gt;().Where(row =&gt; row.RowState == DataRowState.Added).ToArray())
            {
                var parameters = table.Columns.Cast&lt;DataColumn&gt;()
                    .Where(
                        column =>
                        !table.PrimaryKey.Contains(column) ||
                        (table.PrimaryKey.Contains(column) &amp;&amp; !column.AutoIncrement) ||
                        !column.ReadOnly)
                    .ToDictionary(column =&gt; "@" + column.ColumnName.Replace(" ", "_"),
                                  column =&gt; row[column] == DBNull.Value ? string.Empty : row[column]);

                var result = DataAdapter.ExecuteQuery(GetExecCommand(oTable.InsertStoredProc), parameters);
                table.Rows.Remove(row);
                table.Merge(result);
            }
        }

        public static void ExecuteUpdate(DataTable table)
        {
            var oTable = table as IDataTable;
            if (oTable == null)
                return;

            foreach (var row in table.Rows.Cast&lt;DataRow&gt;().Where(row =&gt; row.RowState == DataRowState.Modified))
            {
                var parameters = table.Columns.Cast&lt;DataColumn&gt;()
                    .ToDictionary(column =&gt; "@" + column.ColumnName.Replace(" ", "_"), column =&gt; row[column]);

                DataAdapter.ExecuteNonQuery(GetExecCommand(oTable.UpdateStoredProc), parameters);
                row.AcceptChanges();
            }
        }

        public static void ExecuteDelete(DataTable table)
        {
            var oTable = table as IDataTable;
            if (oTable == null)
                return;

            foreach (var row in table.Rows.Cast&lt;DataRow&gt;().Where(row =&gt; row.RowState == DataRowState.Deleted).Reverse())
            {
                var parameters = table.Columns.Cast&lt;DataColumn&gt;()
                    .Where(column =&gt; table.PrimaryKey.Contains(column))
                    .ToDictionary(column =&gt; "@" + column.ColumnName.Replace(" ", "_"),
                                  column =&gt; row[column, DataRowVersion.Original]);

                DataAdapter.ExecuteNonQuery(GetExecCommand(oTable.DeleteStoredProc), parameters);
                row.AcceptChanges();
            }
        }
        
        #endregion

        #region Internal Methods

        internal static DataTable ExecuteQuery(string query)
        {
            return DataAdapter.ExecuteQuery(query, null);
        }

        internal static void ExecuteQuery(DataTable table, string query)
        {
            ExecuteQuery(table, query, null, true);
        }

        internal static void ExecuteQuery(DataTable table, string storedProc, Dictionary&lt;string, object&gt; parameters)
        {
            ExecuteQuery(table, storedProc, parameters, true);
        }

        internal static void ExecuteQuery(DataTable table, string storedProc, Dictionary&lt;string, object&gt; parameters,
                                          bool clearTable)
        {
            if (clearTable) table.Clear();
            table.Merge(DataAdapter.ExecuteQuery(GetExecCommand(storedProc), parameters));
        }

        internal static IEnumerable&lt;T&gt; ExecuteQuery&lt;T&gt;(DataTable table, string storedProc, Dictionary&lt;string, object&gt; parameters) 
            where T:DataRow
        {
            using (var reader = DataAdapter.ExecuteQueryReader(GetExecCommand(storedProc), parameters))
            {
                var rowValues = new object[reader.FieldCount];
                while (reader.Read())
                {
                    reader.GetValues(rowValues);
                    yield return table.LoadDataRow(rowValues, true) as T;
                }
            }
        }

        internal static void ExecuteNonQuery(string storedProc, Dictionary&lt;string, object&gt; parameters)
        {
            DataAdapter.ExecuteNonQuery(GetExecCommand(storedProc), parameters);
        }

        internal static string GetExecCommand(string spName)
        {
            lock (SchemaCache)
            {
                if (spName.TrimStart().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
                    return spName;
            
                if (SchemaCache.ContainsKey(spName))
                    return SchemaCache[spName];

                var def = spName;
                const string infoSql =
                    "SELECT DISTINCT r.SPECIFIC_NAME, p.PARAMETER_NAME, p.DATA_TYPE, p.CHARACTER_MAXIMUM_LENGTH, p.ORDINAL_POSITION " +
                    "FROM INFORMATION_SCHEMA.ROUTINES r " +
                    "INNER JOIN INFORMATION_SCHEMA.PARAMETERS p ON p.SPECIFIC_NAME = r.SPECIFIC_NAME " +
                    "WHERE r.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA " +
                    "AND p.PARAMETER_MODE = 'IN' AND (r.SPECIFIC_SCHEMA + '.' + r.SPECIFIC_NAME) = @spName " +
                    "ORDER BY p.ORDINAL_POSITION";
                var table = DataAdapter.ExecuteQuery(infoSql, new Dictionary&lt;string, object&gt; {{"@spName", spName}});
                if (table.Rows.Count &gt; 0)
                {
                    var para = (from DataRow row in table.Rows select row["PARAMETER_NAME"].ToString()).ToList();
                    def = "exec " + spName + " " + String.Join(",", para.ToArray());
                    SchemaCache.Add(spName, def);
                    return def;
                }
                return def;
            }
        }
        
        #endregion
    }
}
    </xsl:template>	
</xsl:stylesheet>
